A common problem in managing indexes in database systems is that any transient physical error in failing to read or write to an index page results in placing the whole index in a restrictive state until some kind of recovery procedure is run. There are generally two kinds of restricted states. The first kind of restricted states is where index pages are placed in the restricted state (also referred to herein as logical page list or LPL) due to a physical error.
The second kind of restricted states is where index pages are placed in the restrictive state (referred to as rebuild pending) due to a logical error, such as a software bug that usually manifests itself as a mismatch between the data on the log record and the data on the index page.
The index is placed in restrictive state due to a physical error during redo, a physical error during undo, a logical error during redo, or a logical error during undo. Physical errors during redo or undo occur when logs cannot be applied. Consequently, recovery from the restrictive state comprises reapplying logs.
Since a physical error prevents a log apply to the index, the index manager places the affected pages of the index in restricted state after the buffer manager attempts a retry process to correct for the error. A log record cannot be skipped; therefore, there is no alternative to placing the page in restrictive state. In essence, restrictive state recovery is then a retry process of reapplying the logs.
The pages in the restrictive state are not accessible for normal index operations. Thus taking the entire index offline causes outages of the database since users have no access to the index till the index is repaired. Index recovery from either LPL or from rebuild pending state requires running expensive utilities, which adds to the time the index is offline.
What is needed to provide access to the index in the presence of physical errors and software bugs is a method of placing a portion of the index into restricted access. There currently exists no method for placing a portion of the index into restricted access while maintaining logical consistency of the remainder of the index and preventing users from seeing uncommitted changes in the index.
What is therefore needed is a system, a computer program product, and an associated method for increasing availability of an index. The need for such a solution has heretofore remained unsatisfied. In conventional systems, any logical error would result in keeping the entire index in rebuild pending restrictive state and then later rebuilding the entire index. Rebuilding the entire index could be quite expensive in terms of resources. What is also needed is an improvement that allows a partial rebuild of the index to recover from rebuild pending state.